﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Xml;
using SaaS;
using System.Collections.Generic;
using Microsoft.WindowsAzure.ServiceRuntime;
using System.IO;

namespace SaaS
{
    /// <summary>
    /// Summary description for AddDataService
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class AddDataService : System.Web.Services.WebService
    {
        private List<string> parse(string values)
        {
            int pos, pos2;
            pos2 = -1;
            List<string> v = new List<string>();
            pos = values.IndexOf("|");
            while (pos >= 0)
            {
                v.Add(values.Substring(pos2+1, pos - pos2 - 1));
                pos2 = pos;
                pos = values.IndexOf("|", pos+1);
            }
            v.Add(values.Substring(pos2+1,values.Length - pos2 -1));
            return v;
        }

        [WebMethod]
        public bool addData(string values)
        {
            List<string> v = parse(values);
            if (v.Count != 5)
                return false;
            int selectVal; string selectValue; string colID; string isPriKey; string value;
            selectVal = Convert.ToInt32(v[0]);
            selectValue = v[1];
            colID = v[2];
            isPriKey = v[3];
            value = v[4];
            return addData( selectVal,  selectValue,  colID,  isPriKey,  value);
        }

        [WebMethod]
        public bool addData(int selectVal, string selectValue, string colID, string isPriKey, string value)
        {
            LocalResource myStorage = RoleEnvironment.GetLocalResource("TempStorage");
            string filePath = Path.Combine(myStorage.RootPath, "tempdata.xml");

            string[] temp = selectValue.Split('(', ')');
            bool flag = true;
            switch (temp[1])
            {
                case "int":
                    int i = 0;
                    flag = int.TryParse(value, out i);
                    break;
                case "double":
                    double j = 0.0;
                    flag = Double.TryParse(value, out j);
                    break;
                case "bool":
                    bool k = false;
                    flag = bool.TryParse(value, out k);
                    break;
                default:
                    break;
            }
            if (flag == false)
            {
                return false;
            }
            //////////////////////////////////////////////////////////////////////////
            XmlDocument xmldata = new XmlDocument();
            xmldata.Load(filePath);
            xmldata.DocumentElement.ChildNodes[selectVal].FirstChild.InnerText = value;
            if (colID.Length > 0)
                xmldata.DocumentElement.ChildNodes[selectVal].ChildNodes[1].InnerText = colID;
            xmldata.Save(filePath);

            if (isPriKey.Equals("True", StringComparison.CurrentCultureIgnoreCase))
            {
                //a value was added that was a primary key
                int keysAdded = (int)Session["KeysAdded"];
                Session["KeyCol" + keysAdded.ToString()] = colID;
                Session["KeyVal" + keysAdded.ToString()] = value;
                keysAdded++;
                Session["KeysAdded"] = keysAdded;
            }
            return true;
        }

        [WebMethod]
        public string finish(string values)
        {
            List<string> v = parse(values);
            if (v.Count != 2)
                return "Arguments not equal to 2";
            string dataComment; string tableID;
            dataComment = v[0];
            tableID = v[1];
            return finish( dataComment,  tableID);
        }

        [WebMethod]
        public string finish(string dataComment, string tableID)
        {
            LocalResource myStorage = RoleEnvironment.GetLocalResource("TempStorage");
            string filePath = Path.Combine(myStorage.RootPath, "tempdata.xml");
            XmlDocument xmldata = new XmlDocument();
            xmldata.Load(filePath);
            String datString = xmldata.OuterXml;
            int dataID;
            int ver;
            //open database connection
            MiniSaaSDB db = new MiniSaaSDB();
            db.open();
            if (db.isOpen())
            {
                String query;
                int keysAdded = (int)Session["KeysAdded"];
                System.Data.SqlClient.SqlDataReader reader;

                if (keysAdded > 0)
                {
                    //check primary key constraints
                    bool duplicate = true;
                    for (int i = 0; i < keysAdded; i++)
                    {
                        query = "SELECT * FROM KEYS k INNER JOIN DATA d ON k.Data_ID = d.Data_ID " +
                        "WHERE d.Table_ID = " + tableID;
                        query += " AND k.Column_ID = " + Session["KeyCol" + i.ToString()].ToString() +
                            " AND k.Value = '" + Session["KeyVal" + i.ToString()].ToString() + "'";
                        reader = db.executeQuery(query);
                        if (reader != null && !reader.HasRows)
                        {
                            //a distinct row is here!
                            duplicate = false;
                            break;
                        }
                    }
                    if (duplicate)
                    {
                        return "Duplicate primary key found - Could not add data";
                    }
                }

                int rows;
                reader = db.executeQuery("SELECT MAX(Table_Version) FROM META WHERE Table_ID = " + tableID);
                if (!reader.HasRows)
                {
                    //Error - table does not exist
                    db.close();
                    return "ERROR - Table does not exist";
                }
                else
                {
                    reader.Read();
                    ver = (int)reader[0];
                }
                reader.Close();
                reader = db.executeQuery("SELECT MAX(Data_ID)+1 FROM DATA");
                if (!reader.HasRows)
                {
                    dataID = 1;
                }
                else
                {
                    reader.Read();
                    dataID = (int)reader[0];
                }
                reader.Close();
                query = "INSERT INTO DATA (Data_ID, Table_ID, Table_Version, Data_Rows, Data_Comment) " +
                    "VALUES (" + dataID.ToString() + ", " + tableID + ", " + ver.ToString() + ", '" +
                    datString + "', ";
                if (dataComment != null && dataComment.Length > 0)
                    query = query + "'" + dataComment + "');";
                else
                    query = query + "NULL);";
                rows = db.executeNonQuery(query);
                if (rows <= 0)
                {
                    //No rows affected
                    db.close();
                    return "No rows affected";
                }

                if (keysAdded > 0)
                {
                    //Keys were added; add data to KEYS table
                    for (int i = 0; i < keysAdded; i++)
                    {
                        query = "INSERT INTO KEYS (Data_ID, Column_ID, Value) VALUES (" + dataID.ToString() +
                            ", " + Session["KeyCol" + i.ToString()].ToString() + ", '" + Session["KeyVal" + i.ToString()].ToString() +
                            "');";
                        rows = db.executeNonQuery(query);
                    }
                }
                
                db.close();
            }
            else
            {
                //Error opening DB
                return "ERROR - Could not reach database";
            }
            return "";
        }
    }
}
